package com.logisticsmanagement.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.logisticsmanagement.domain.City;
import com.logisticsmanagement.domain.Route;
import com.logisticsmanagement.utils.DataSource;
import com.logisticsmanagement.utils.DatabaseUtil;

public class RouteDao {
	/**
 	 * query information of a route by startCityID and endCityID
	 * @param id
	 * @return
	 */
  	public Route selectById(Integer startID, Integer endID){

		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		Route route = null;
		
		try {
			conn= DataSource.getConnection();   //get connection to database
			if(conn == null)
				return null;
			sta = conn.prepareStatement(	 
					"select * from Route where startCityID=? and endCityID=? ");	 //get operation statement object of database 
			sta.setInt(1, startID);
			sta.setInt(2, endID);
			rs = sta.executeQuery();	 //query database, return ResultSet
			if(rs.next()){
				route =  new Route();	  
				route.setStartCityID(startID);
				route.setEndCityID(endID);
				route.setNextCityID(rs.getInt(3));
				route.setPricePerKG(rs.getDouble(4));
				route.setPrepareDelete(rs.getBoolean(5));
				route.setIsDelete(rs.getBoolean(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return route;
	}
	
	/**
	 * add route
	 * @param route
	 */
	public Boolean add(Route route){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to object
			if(conn == null)
				return null;
			sta = conn.prepareStatement(
					"insert into Route(startCityID,endCityID,"
					+ "nextCityID,pricePerKG,prepareDelete,isDelete) "
					+ "values(?,?,?,?,?,?) ");	 //get operation statement object of database
			sta.setInt(1, route.getStartCityID());
			sta.setInt(2, route.getEndCityID());
			sta.setInt(3, route.getNextCityID());
			sta.setDouble(4, route.getPricePerKG());
			sta.setBoolean(5, (route.getPrepareDelete() == true)?true:false);
			sta.setBoolean(6, (route.getIsDelete() == true)?true:false);
			return (sta.executeUpdate()==1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * update information of route. if update successfully, return true.
	 * @param city
	 * @return
	 */
	public Boolean update(Route route){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return false;
			sta = conn.prepareStatement(
 					"update Route set startCityID=? and endCityID=? and "
					+ "nextCityID=? and pricePerKG=? and prepareDelete=? and "
					+ "isDelete=? where startCityID=? and endCityID=?");	//get operation statement object of database
			sta.setInt(1, route.getStartCityID());
			sta.setInt(2, route.getEndCityID());
			sta.setInt(3, route.getNextCityID());
			sta.setDouble(4, route.getPricePerKG());
			sta.setBoolean(5, (route.getPrepareDelete() == true)?true:false);
			sta.setBoolean(6, (route.getIsDelete() == true)?true:false);
			sta.executeUpdate();	//execute，update database
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * count amount of route with certain attribute
	 * @param route
	 * @return
	 */
	public Integer selectCount(Route route){
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = DatabaseUtil.getSelcectCountStatement(route, conn);	//define sql query statement, then get operation statement object of database
			rs = sta.executeQuery();		//query database, return ResultSet
			if(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try { 	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
	/**
	 * the criteria query,the paging query 
	 * @param route
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
 	public List<Route> selectPage(Route route,int nowPage,int pageSize, String orderBy){
		
 		List<Route> ret = new ArrayList<Route>();
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		if(orderBy == null)
			orderBy = "startCityID,endCityID";
		
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			//define sql query statement, then get operation statement object of database
			sta = DatabaseUtil.getSelectPageStatment(route, conn, nowPage, pageSize, orderBy);	 
			rs = sta.executeQuery();		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()){
				Route retRoute =  new Route();
				retRoute.setStartCityID(rs.getInt(1));
				retRoute.setEndCityID(rs.getInt(2));
				retRoute.setNextCityID(rs.getInt(3));
				retRoute.setPricePerKG(rs.getDouble(4));
				retRoute.setPrepareDelete(rs.getBoolean(5));
				retRoute.setIsDelete(rs.getBoolean(6));
				ret.add(retRoute);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
 	
 	/**
	 * query all cities along a route 
	 * @param route
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
  	public List<City> selectByStartEnd(Integer startCityID, Integer endCityID){
		
 		List<City> ret = new ArrayList<City>();
 		CityDao cityDao = new CityDao();
  		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement sta = null;
		
		ret.add(cityDao.selectById(startCityID));
		
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
 			while(startCityID != endCityID) {
				sta = conn.prepareStatement("select * from City where "
						+ "cityID=(select nextCityID from Route where "
						+ "stratCityID=? and endCityID=?) ");	//get operation statement object of database
				sta.setInt(1, startCityID);
				sta.setInt(2, endCityID);
				rs = sta.executeQuery();		//query database, return ResultSet
				if(rs.next()) {
					City retCity = new City();
					retCity.setCityID(rs.getInt(1));
					retCity.setName(rs.getString(2));
					retCity.setPrepareDelete(rs.getBoolean(3));
					retCity.setIsDelete(rs.getBoolean(4));
					retCity.setAsset(rs.getDouble(5));
					ret.add(retCity);
					startCityID = retCity.getCityID();
				}	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
}
